package com.excel.util.antichem;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import javax.imageio.ImageIO;
import javax.imageio.stream.ImageInputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class ReportBuilder 
{
	//==============================================================================//
	private static Logger log = LoggerFactory.getLogger(ReportBuilder.class);
	FileOutputStream fileOutputStream = null;
	HSSFWorkbook workBook = null;
	HSSFSheet sheet = null;
	HSSFPatriarch patriarch = null;
	private Integer rowNum;
	//==============================================================================//
	
	
	//==============================================================================//
	/**
	 * @用途：重载+1。加载一个已经存在的模板，将生成的内容保存到 workbook中
	 * @参数：String templateFile：指索要加载的模板的路径，如："C:/Tamplates/texting-1.xls"
	 * @用法：templateFile： String templateFile_Name1 = "C:/Tamplates/texting-1.xls"               
	 * @author Yangcl
	 */
	public void loadTemplate(String templateURL)
	{
		if (templateURL == null || templateURL.trim().equals(""))
		{
			// 文件不能为空提示
			throw new RuntimeException("文件模板路径不能为空");
		}

		try
		{
			FileInputStream templateFile_Input = new FileInputStream(templateURL);			
			POIFSFileSystem fs = new POIFSFileSystem(templateFile_Input);
			workBook = new HSSFWorkbook(fs);
			sheet = workBook.getSheetAt(0);
			setRowNum(sheet.getLastRowNum());
		}
		catch (Exception e)
		{
			log.error("loadTemplate()方法异常，文件加载失败：", e.toString());
		}
	}
	/**
	 * @param templateURL
	 * 加载模板，并写入活动的表尾
	 */
	public void loadTemplateFoot(String templateURL,Integer rowNum,String[] str,String[] hejiStr,Integer cellNum)
	{
		if (templateURL == null || templateURL.trim().equals(""))
		{
			// 文件不能为空提示
			throw new RuntimeException("文件模板路径不能为空");
		}
		
		try
		{
			FileInputStream templateFile_Input = new FileInputStream(templateURL);			
			POIFSFileSystem fs = new POIFSFileSystem(templateFile_Input);
			workBook = new HSSFWorkbook(fs);
			sheet = workBook.getSheetAt(0);
			for(int i=0;i<str.length;i++){
				sheet.addMergedRegion(new Region(rowNum+2+i,(short)2,rowNum+2+i,(short)(2+cellNum)));//合并行
				writeInTemplateHebingBiaoti(str[i], rowNum+2+i, 1);
				
				sheet = workBook.getSheetAt(0);
				HSSFRow row = sheet.getRow(rowNum+2+i); 
				HSSFCellStyle style = workBook.createCellStyle();
				style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
				style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
				style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
				style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
				style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
				style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
				for(int j=2;j<=2+cellNum;j++){
					HSSFCell cell = row.createCell(j);
					if (j==2) {
						cell.setCellValue(hejiStr[i]);
					}else {
						cell.setCellValue("");
					}
					cell.setCellStyle(style);
				}
			}
			sheet.addMergedRegion(new Region(rowNum+2,(short)0,rowNum+str.length+1,(short)0));//合并列
			
			sheet = workBook.getSheetAt(0);
			HSSFCellStyle style = workBook.createCellStyle();
			HSSFFont font = createCellFont(workBook);
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style.setFont(font);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
			
			for(int j=rowNum+2;j<=rowNum+str.length+1;j++){
				HSSFRow row = sheet.getRow(j);
				HSSFCell cell = row.createCell(0);
				if (j==rowNum+2) {
					style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
					style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
					cell.setCellValue("合计");
				}else {
					cell.setCellValue("");
				}
				
				cell.setCellStyle(style);
			}
			setRowNum(sheet.getLastRowNum());
		}
		catch (Exception e)
		{
			log.error("loadTemplate()方法异常，文件加载失败：", e.toString());
		}
	}
	public void loadTemplate(InputStream templateStream)
	{
		try
		{
			POIFSFileSystem fs = new POIFSFileSystem(templateStream);
			workBook = new HSSFWorkbook(fs);
			sheet = workBook.getSheetAt(0);
		}
		catch (Exception e)
		{
			log.error("loadTemplate()方法异常，文件加载失败：", e.toString());
		}
	}
	//==============================================================================//
	private void checkAndAddSheet(int sheetIndex)
	{
		if (sheetIndex + 1 > workBook.getNumberOfSheets())
		{
			for (int i = 0; i < sheetIndex + 1 - workBook.getNumberOfSheets(); i++)
			{
				sheet = workBook.createSheet();
			}
		}
		else
		{
			sheet = workBook.getSheetAt(sheetIndex);
		}
	}
	//==============================================================================//
	/**
	 * 写入非图片格式信息，重载+3
	 * 
	 * @描述：这是一个实体类，提供了相应的接口，用于操作Excel，在任意坐标处写入数据。
	 * @参数：String newContent：你要输入的内容 
	 * 		  int beginRow ：行坐标，Excel从 0 算起 
	 * 		  int beginCol：列坐标，Excel从 0 算起         
	 * @author Yangcl
	 */
	public void writeInTemplate(String newContent, int beginRow, int beginCell)
	{
		sheet = workBook.getSheetAt(0);
		HSSFRow row = sheet.getRow(beginRow); 
		if (null == row)
		{
			row = sheet.createRow(beginRow);
		} 
		HSSFCell cell = row.getCell(beginCell); 
		if (null == cell)
		{
			cell = row.createCell(beginCell);
		} 
		cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
		cell.setCellValue(newContent);
	}
	public String readTemplate(int beginRow, int beginCell)
	{
		sheet = workBook.getSheetAt(0);
		HSSFRow row = sheet.getRow(beginRow); 
		if (null == row)
		{
			row = sheet.createRow(beginRow);
		} 
		HSSFCell cell = row.getCell(beginCell); 
		if (null == cell)
		{
			cell = row.createCell(beginCell);
		} 
		cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
		return cell.getStringCellValue();
	}
	
	/**
	 * 写入非图片格式信息,针对Excel存在多个sheet的情况。
	 */
	public void writeInTemplate(int sheetIndex, String newContent,	int beginRow, int beginCell)
	{
		checkAndAddSheet(sheetIndex);
		HSSFRow row = sheet.getRow(beginRow);
		if (null == row)
		{ 
			row = sheet.createRow(beginRow);
		}
		HSSFCell cell = row.getCell(beginCell);
		if (null == cell)
		{
			cell = row.createCell(beginCell); 
		} 
		if(newContent.contains("fontyes")){
			HSSFCellStyle style = workBook.createCellStyle();
			HSSFFont font = createCellFont(workBook);
			style.setFont(font);
			cell.setCellStyle(style);
			newContent = newContent.substring(7,newContent.length());
		}
		
		HSSFCellStyle style = workBook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框   
		cell.setCellStyle(style);
		
		cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
		cell.setCellValue(newContent);
	}
	/**
	 * 写入非图片格式信息,针对Excel存在多个sheet的情况。
	 */
	public void writeInTemplateDouble(int sheetIndex, Double newContent,int beginRow, int beginCell)
	{
		checkAndAddSheet(sheetIndex);
		HSSFRow row = sheet.getRow(beginRow);
		if (null == row)
		{ 
			row = sheet.createRow(beginRow);
		}
		HSSFCell cell = row.getCell(beginCell);
		if (null == cell)
		{
			cell = row.createCell(beginCell); 
		} 
		if(Double.toString(newContent).contains("fontyes")){
			HSSFCellStyle style = workBook.createCellStyle();
			HSSFFont font = createCellFont(workBook);
			style.setFont(font);
			cell.setCellStyle(style);
			newContent = Double.parseDouble(Double.toString(newContent).substring(7,Double.toString(newContent).length()));
		}
		
		HSSFCellStyle style = workBook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框   
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框   
		cell.setCellStyle(style);
		
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); 
		cell.setCellValue(newContent);
	}
	
    public static HSSFFont createCellFontHeji(HSSFWorkbook workbook){
        // *生成一个字体
        HSSFFont font = workbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 10);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        return font;
    }
	
	//合计单元格里面的值，自己写
	public void writeInTemplateHebingValue(String newContent, int beginRow, int beginCell)
	{
		sheet = workBook.getSheetAt(0);
		HSSFRow row = sheet.getRow(beginRow); 
		if (null == row)
		{
			row = sheet.createRow(beginRow);
		} 
		HSSFCell cell = row.getCell(beginCell); 
		if (null == cell)
		{
			cell = row.createCell(beginCell);
		}
		
		cell.setCellValue(newContent);
	}
	
	//合计标题的单元格，自己写
	public void writeInTemplateHebingBiaoti(String newContent, int beginRow, int beginCell)
	{
		sheet = workBook.getSheetAt(0);
		HSSFRow row = sheet.getRow(beginRow); 
		if (null == row)
		{
			row = sheet.createRow(beginRow);
		} 
		HSSFCell cell = row.getCell(beginCell); 
		if (null == cell)
		{
			cell = row.createCell(beginCell);
		}
		
		HSSFCellStyle style = workBook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框   
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框   
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框   
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框  
		HSSFFont font = createCellFont(workBook);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		cell.setCellStyle(style);
		
		cell.setCellValue(newContent);
	}
	
	/*
	 * 字体设置
     */
	    public static HSSFFont createCellFont(HSSFWorkbook workbook){
	        // *生成一个字体
	        HSSFFont font = workbook.createFont();
	        // 字体颜色
//	        font.setColor(HSSFColor.VIOLET.index);
	        // 字体大小
	        //font.setFontHeightInPoints((short) 12);
	        // 字体加粗
	        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	        // 是否使用斜体
//	        font.setItalic( true );
	        // 是否使用划线
	        //font.setStrikeout(true);
	        // 字体名字
	        font.setFontName("黑体");
	        return font;
	    }
	//==============================================================================//
	/**
	 * 写入图片格式信息，重载+1
	 * 
	 * @param dx1：第一个cell开始的X坐           
	 * @param dy1：第一个cell开始的Y坐标            
	 * @param dx2：第二个cell开始的X坐标        
	 * @param dy2：第二个cell开始的Y坐标
	 * @param col1：图片的左上角放在第几个列cell (the column(o based); of the first cell)
	 * @param row1：图片的左上角放在第几个行cell (the row(o based); of the first cell)
	 * @param col2：图片的右下角放在第几个列cell (the column(o based); of the second cell)
	 * @param row2：图片的右下角放在第几个行cell (the row(o based); of the second cell)
	 *            
	 * @描述：这是一个实体类，提供了相应的接口，用于操作Excel，在任意坐标处写入数据。
	 * @参数： String imageFileURL：他接受一个外界传入的图片路径，图片以 *.jpeg 形式存在。
	 * @用法： ReportBuilder twi = new ReportBuilder(); 
	 *     	  String imageFileURL ="D:/workspace/Tamplates/1.jpeg"; 
	 *        twi.writeInTemplate(imageFileURL ,0,0, 0, 0, (short)6, 5, (short)8, 8);     
	 * @author Yangcl
	 */
	public void writeInTemplate(String imageFileURL, int dx1, int dy1, int dx2,int dy2, short col1, int row1, short col2, int row2)
	{
		BufferedImage bufferImage = null;

		// 写入图片格式信息
		try
		{
			ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
			// 先把读入的图片放到第一个 ByteArrayOutputStream 中，用于产生ByteArray
			File fileImage = new File(imageFileURL);
			bufferImage = ImageIO.read(fileImage);
			ImageIO.write(bufferImage, "JPG", byteArrayOutputStream);
			System.out.println("ImageIO 写入完成");

			// 准备插入图片
			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
			HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2,
					col1, row1, col2, row2);

			// 插入图片
			byte[] pictureData = byteArrayOutputStream.toByteArray();
			int pictureFormat = HSSFWorkbook.PICTURE_TYPE_JPEG;
			int pictureIndex = workBook.addPicture(pictureData, pictureFormat);
			patriarch.createPicture(anchor, pictureIndex);

		}
		catch (Exception e)
		{
			log.error("IO Erro：", e);
		}
		finally
		{
			if (fileOutputStream != null)
			{
				try
				{
					fileOutputStream.close();
				}
				catch (IOException io)
				{
					log.error(io.toString());
				}
			}
		}
	}
	/**
	 * 图片流写入
	 * @param imageInputStream
	 * @param dx1
	 * @param dy1
	 * @param dx2
	 * @param dy2
	 * @param col1
	 * @param row1
	 * @param col2
	 * @param row2
	 */
	public void writeInTemplate(ImageInputStream imageInputStream, int dx1,int dy1, int dx2, int dy2, short col1, int row1, short col2,	int row2)
	{
		BufferedImage bufferImage = null;
		// 写入图片格式信息
		try
		{
			ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
			// 先把读入的图片放到一个 ByteArrayOutputStream 中，用于产生ByteArray
			bufferImage = ImageIO.read(imageInputStream);
			ImageIO.write(bufferImage, "JPG", byteArrayOutputStream);
			System.out.println("ImageIO 写入完成");

			// 准备插入图片
			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
			HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2,
					col1, row1, col2, row2);

			// 插入图片
			byte[] pictureData = byteArrayOutputStream.toByteArray();
			int pictureFormat = HSSFWorkbook.PICTURE_TYPE_JPEG;
			int pictureIndex = workBook.addPicture(pictureData, pictureFormat);
			patriarch.createPicture(anchor, pictureIndex);
		}
		catch (Exception e)
		{
			log.error("IO Erro：", e);
		}
		finally
		{
			if (fileOutputStream != null)
			{
				try
				{
					fileOutputStream.close();
				}
				catch (IOException io)
				{
				}
			}
		}
	}
	//==============================================================================//
	/**
	 * 保存模板 
	 * @param templateFile
	 * @描述：这个方法用于保存workbook(工作薄)中的内容，并写入到一个Excel文件中
	 * @参数：String templateFile：取得已经保存的类模板 路径名称
	 * 
	 * @用法：templateFile：String templateFile_Name1 = "C:/Tamplates/texting-1.xls"
	 *        TemplateAdapter ta = new TemplateAdapter();
	 *        ta.SaveTemplate(templateFile_Name1);
	 */
	public void SaveTemplate(String templateFile)
	{
		try
		{
			// 建立输出流
			fileOutputStream = new FileOutputStream(templateFile);
			workBook.write(fileOutputStream);
		}
		catch (Exception e)
		{
			log.error("IO Erro", e);
		}
		finally
		{
			if (fileOutputStream != null)
			{
				try
				{
					fileOutputStream.close();
				}
				catch (IOException io)
				{
					log.error(io.toString());
				}
			}
		}
	}
	public Integer getRowNum() {
		return rowNum;
	}
	public void setRowNum(Integer rowNum) {
		this.rowNum = rowNum;
	}
	
}










